﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VentaElectrodomesticos.Contrato;
using System.Data.SqlClient;
using System.Data;

namespace VentaElectrodomesticos.Datos
{
    public class QueryBuilder<T> where T : Entity, new()
    {        
        private Dictionary<String, object> properties = new Dictionary<string,object>();
        private String objectName;

        public QueryBuilder(T entity, String tableName)
        {
            this.properties = Introspection<T>.getRunTimeProperties(entity);
            if (tableName != null)
            {
                this.objectName = tableName;
            }
            else 
            {
                this.objectName = Introspection<T>.getType(entity).Name;
            }
            
        }
        public String getSqlInsert()
        {
            String names = "";
            String values = "";
            foreach (KeyValuePair<String, object> pair in this.properties)
            {
                if (IsNotValidInsertProperty(pair.Key, pair.Value))
                    continue;
                names += "" + pair.Key + ",";
                values += "'" + this.formatValue(pair.Value) + "',";
            }
            names = names.Substring(0, names.Length - 1);
            values = values.Substring(0, values.Length - 1);
            String sql = "Insert Into " + Config.tableSpace + this.objectName.ToLower() + " (" + names + ") Values (" + values + ")";
            return sql;
        }

        public String getSqlUpdate()
        {
            String namesValues = "";
            String id = "";
            foreach (KeyValuePair<String, object> pair in this.properties)
            {
                if (pair.Key == "Id")
                {
                    id = pair.Value.ToString();
                    continue;
                }
                else if (IsNotValidInsertProperty(pair.Key, pair.Value))
                    continue;                
                namesValues += pair.Key + " = '" + this.formatValue(pair.Value) + "',";
            }
            namesValues = namesValues.Substring(0, namesValues.Length - 1);
            String sql = "Update " + Config.tableSpace + this.objectName.ToLower() + " Set " + namesValues + " Where Id = " + id;
            return sql;
        }
        public List<SqlParameter> getStoredProcedureParameters()
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (KeyValuePair<String, object> pair in this.properties)
            {
                if (IsNotValidProperty(pair.Key, pair.Value))
                    continue;
                SqlParameter param = new SqlParameter("@" + pair.Key, this.formatValue(pair.Value));
                parameters.Add(param);
            }
            return parameters;
        }
        private String formatValue(object value)
        {
            return value.ToString().Replace(",",".");
        }
        private static bool IsNotValidProperty(String key, Object obj)
        {
            return key == "Id" || obj == null ||
                   obj.ToString() == string.Empty || obj.GetType().IsAssignableFrom(typeof(Entity));
        }
        private static bool IsNotValidInsertProperty(String key, Object obj)
        {
            return IsNotValidProperty(key, obj) || key.StartsWith("_");
        }
        private String getId()
        {
            return this.properties.Where(p => p.Key == "Id").FirstOrDefault().Value.ToString();
        }
        public String getSqlDelete(bool logicalDelete)
        {
            String id = getId();
            String sql = null;
            if (logicalDelete)
            {
                sql = "Update " + Config.tableSpace + this.objectName.ToLower() + " SET inactivo = 'true' WHERE Id = " + id;
            }
            else {
                sql = "Delete From " + Config.tableSpace + this.objectName.ToLower() + " Where Id = " + id;
            }
            
            return sql;
        }
        public String getSqlGetById()
        {
            String id = getId();
            String sql = "Select * From " + Config.tableSpace + this.objectName.ToLower() + " Where Id = " + id;
            return sql;
        }
        public String getSqlGetByAll()
        {
            return this.getSqlGetByAll(false);
        }

        public String getSqlGetByAll(Boolean onlyAvailable)
        {
            String sql = "Select * From " + Config.tableSpace + this.objectName.ToLower();

            if (onlyAvailable) {
                sql += " where inactivo = 'false'";
            }

            return sql;
        }

        public String getSqlInsertAndReturnId()
        {
            String sql = this.getSqlInsert();
            sql += "; SELECT @@Identity as Id";
            return sql;
        }
    }
}
